package com.example.statement;


import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;

public class StatementTest {

    // Statement类的弊端：存在拼接SQL和SQL注入问题
    @Test
    public void testLogin() {
        Scanner scanner = new Scanner(System.in);
        System.out.print("用户名：");
        String userName = scanner.nextLine();
        System.out.print("密 码：");
        String password = scanner.nextLine();

        // 如下输入出现SQL注入：
        // 用户名：1' or
        // 密码：=1 or '1' and '1
        String sql = "SELECT user, password FROM user_table WHERE user='" + userName + "' AND password='" + password + "'";
        User user = get(sql, User.class);
        if (user != null) {
            System.out.println("登录成功！");
        } else {
            System.out.println("登录失败！");
        }
    }

    private static <T> T get(String sql, Class<T> clazz) {
        T t = null;

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            InputStream inputStream = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            if (resultSet.next()) {
                t = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int columnCount = resultSetMetaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnLabel = resultSetMetaData.getColumnLabel(i);
                    String columnValue = resultSet.getString(i);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return t;
    }

}
